﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Enum;
using BySoft.Server.Common.WebCommon;

using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;
using BySoft.Server.Consts;

namespace BySoft.Server.DataAccess
{
    public partial class SysUserDA
    {

        #region GetSupervisorByID
        /// <summary>
        /// 取得用户的上级用户实体
        /// </summary>
        /// <param name="id">实体关键字</param>
        /// <returns></returns>
        public static SysUserEntity GetSupervisorByID(string id)
        {
            string strSQL = "SELECT sup.* FROM " + SysUserTable.C_TABLENAME + " sup INNER JOIN " + SysUserTable.C_TABLENAME + " und ON sup." + SysUserTable.C_USERID + " = und." + SysUserTable.C_UPPERUSERID;
            WhereBuilder wb = new WhereBuilder(strSQL);
            wb.AddCondition(string.Format("und." + SysUserTable.C_USERID + "='{0}'", id));
            SysUserEntity userEntity = DataAccessCommon.SelectSingle<SysUserEntity>(wb);
            //如果找不到上级，则返回自己  modify by Pater at 2009-09-24
            if (userEntity != null)
                return userEntity;
            else
                return SysUserDA.GetSysUserByID(id);
        }

        #endregion

        /// <summary>
        ///  获得用户相关信息
        /// </summary>
        /// <param name="loginId">LoginId</param>
        /// <returns></returns>
        public static SysUserEntity GetUserLoginInfo(string loginId)
        {

            StringBuilder strSQL = new StringBuilder("SELECT ");

            strSQL.Append(" U." + SysUserTable.C_CREATEDATE);
            strSQL.Append(" ,U." + SysUserTable.C_CREATEUSERID);
            strSQL.Append(" ,U." + SysUserTable.C_DEPARTMENTID);
            strSQL.Append(" ,U." + SysUserTable.C_JOBTITLEID);
            strSQL.Append(" ,U." + SysUserTable.C_EMPLOYEETYPE);
            strSQL.Append(" ,U." + SysUserTable.C_ISDEFAULTPWD);
            strSQL.Append(" ,U." + SysUserTable.C_LASTESTLOGINDATE);
            strSQL.Append(" ,U." + SysUserTable.C_MAIL);
            strSQL.Append(" ,U." + SysUserTable.C_MOBILE);
            strSQL.Append(" ,U." + SysUserTable.C_MODIFYDATE);
            strSQL.Append(" ,U." + SysUserTable.C_MODIFYUSERID);
            strSQL.Append(" ,U." + SysUserTable.C_PASSWORD);
            strSQL.Append(" ,U." + SysUserTable.C_PASSWORDCHANGEDATE);
            strSQL.Append(" ,U." + SysUserTable.C_STATUS);
            strSQL.Append(" ,U." + SysUserTable.C_TEL);
            strSQL.Append(" ,U." + SysUserTable.C_UNITS);
            strSQL.Append(" ,U." + SysUserTable.C_UPPERUSERID);
            strSQL.Append(" ,U." + SysUserTable.C_USERID);
            strSQL.Append(" ,U." + SysUserTable.C_USERLOGINID);
            strSQL.Append(" ,U." + SysUserTable.C_USERNAME);
            strSQL.Append(" ,U." + SysUserTable.C_USERNAMEPINYIN);
            strSQL.Append(" ,U." + SysUserTable.C_USERNAMEPYFIRSTCHAR);
            strSQL.Append(" ,U." + SysUserTable.C_DEPARTMENTID);
            strSQL.Append(" ,U." + SysUserTable.C_JOBTITLEID);
            strSQL.Append(" ,U." + SysUserTable.C_ISGENERAL);

            strSQL.Append(" FROM " + SysUserTable.C_TABLENAME + " U ");
            strSQL.Append(" WHERE lower(U." + SysUserTable.C_USERID + ")='" + loginId.ToLower().Replace("'", "''") + "'");
            strSQL.Append(" or lower(U." + SysUserTable.C_USERLOGINID + ")='" + loginId.ToLower().Replace("'", "''") + "'");
            //tina.su add 有效的用户才能登录
            strSQL.Append(" and  U." + SysUserTable.C_STATUS + "='1'");

            return DataAccessCommon.SelectSingle<SysUserEntity>(strSQL.ToString());
        }

        #region GetAll
        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <returns>返回所有数据</returns>
        public static DataTable GetAllUserInfo( string userName, string userId, string status,
                                                   int pageIndex,
                                                   int pageSize,
                                                   out int count)
        {
            try
            {
                #region SQL Server
                DataTable dt = new DataTable();
                StringBuilder sql = new StringBuilder();
                sql.Append(" SELECT ");
                sql.Append(" U." + SysUserTable.C_USERID);
                sql.Append(",U." + SysUserTable.C_USERNAME);
                sql.Append(",U." + SysUserTable.C_USERNAMEPINYIN);
                sql.Append(",U." + SysUserTable.C_STATUS);
                sql.Append(",U." + SysUserTable.C_DEPARTMENTID);
                sql.Append(",U." + SysUserTable.C_JOBTITLEID);
                sql.Append(",U." + SysUserTable.C_ISGENERAL);
                sql.Append(",U.UserID AS ID,U.UserloginID AS LOGINID, U.UserName AS NAME");

                sql.Append(" FROM " + SysUserTable.C_TABLENAME + " U ");

                //string strCondition = " WHERE U." + SysUserTable.C_STATUS + "=1";
                string strCondition = " WHERE 1=1";
                strCondition += " and U.UserID!='" + CommonConsts.ADMIN_USER + "' ";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(userId))
                {
                    strCondition += " and U." + SysUserTable.C_USERID + " like @userId";
                    dpc.AddWithValue("@userId", "%" + userId + "%");
                }
              
                if (!string.IsNullOrEmpty(status))
                {
                    strCondition += " and U." + SysUserTable.C_STATUS + "=@status";
                    dpc.AddWithValue("@status", status);
                }
                if (!string.IsNullOrEmpty(userName))
                {
                    strCondition += " and U." + SysUserTable.C_USERNAME + " like @userName";
                    dpc.AddWithValue("@userName", "%" + userName + "%");
                }
                sql.Append(strCondition);
                sql.Append(" order BY  U." + SysUserTable.C_USERNAMEPINYIN);

                return DataAccessCommon.ExecuteDataTablePaging(sql.ToString(), pageIndex, pageSize, dpc, out  count);

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetAllUserInfo(string usertype, string userName, string userId, string status,
                                                   int pageIndex,
                                                   int pageSize,
                                                   out int count)
        {
            try
            {
                #region SQL Server
                DataTable dt = new DataTable();
                StringBuilder sql = new StringBuilder();
                sql.Append(" SELECT USERID as CheckUser,USERNAME as CheckUserName,USERID as BackupPerson,USERNAME as BackupPersonName,");
                sql.Append(" U." + SysUserTable.C_USERID);
                sql.Append(",U." + SysUserTable.C_USERNAME);
                sql.Append(",U." + SysUserTable.C_USERNAMEPINYIN);
                sql.Append(",U." + SysUserTable.C_STATUS);
                sql.Append(",U." + SysUserTable.C_EMPLOYEETYPE);
                sql.Append(",U." + SysUserTable.C_DEPARTMENTID);
                sql.Append(",U." + SysUserTable.C_JOBTITLEID);

                sql.Append(" FROM " + SysUserTable.C_TABLENAME + " U ");

                //string strCondition = " WHERE U." + SysUserTable.C_STATUS + "=1";
                string strCondition = " WHERE 1=1";
                strCondition += " and U.UserID!='" + CommonConsts.ADMIN_USER + "' ";
                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(userId))
                {
                    strCondition += " and U." + SysUserTable.C_USERID + " like @userId";
                    dpc.AddWithValue("@userId", "%" + userId + "%");
                }
                if (!string.IsNullOrEmpty(usertype))
                {
                    strCondition += " and U." + SysUserTable.C_EMPLOYEETYPE + "=@usertype";
                    dpc.AddWithValue("@usertype", usertype);
                }
                if (!string.IsNullOrEmpty(status))
                {
                    strCondition += " and U." + SysUserTable.C_STATUS + "=@status";
                    dpc.AddWithValue("@status", status);
                }
                if (!string.IsNullOrEmpty(userName))
                {
                    strCondition += " and U." + SysUserTable.C_USERNAME + " like @userName";
                    dpc.AddWithValue("@userName", "%" + userName + "%");
                }
                sql.Append(strCondition);
                sql.Append(" ORDER BY  U." + SysUserTable.C_USERNAMEPINYIN);

                return DataAccessCommon.ExecuteDataTablePaging(sql.ToString(), pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }
        #endregion

        public static int ChangeUserStatus(string userId, string status)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = "Update " + SysUserTable.C_TABLENAME + " Set ";
                sSql += SysUserTable.C_STATUS + " =@status";
                sSql += "," + SysUserTable.C_MODIFYDATE + " =getdate()";
                sSql += " Where " + SysUserTable.C_USERID + " =@userId";

                parameters.AddWithValue("@status", status);
                parameters.AddWithValue("@userId", userId);

                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }
        }

        public static int ResetPwd(string userId, string pwd)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = "Update " + SysUserTable.C_TABLENAME + " Set ";
                sSql += SysUserTable.C_PASSWORD + " =@pwd ,";
                sSql += SysUserTable.C_ISDEFAULTPWD + " = '1'";
                sSql += " Where " + SysUserTable.C_USERID + " =@userId";

                parameters.AddWithValue("@pwd", pwd);
                parameters.AddWithValue("@userId", userId);

                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }
        }


        public static DataTable GetLowerUser(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string sSql = "select * from( SELECT * FROM fn_FindUserAndAllChildUserAndStatus(@userId)) T ";
            sSql += " where T.USERID !=@userId ";
            sSql += " AND T. STATUS  ='1'";
            parameters.AddWithValue("@userId", userId);

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static Boolean CanUserRole(string userId)
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string sSql = " SELECT COUNT(UserID) FROM SYS_User WHERE UserID<>@UserID AND CanUseRole='1' ";
            parameters.AddWithValue("@userId", userId);

            object obj = DataAccessCommon.SelectSingleObject(sSql, parameters);
            if (Convert.ToInt32(obj) < 5)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static DataTable Select()
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    string strcmd = "SELECT a.UserID, a.UserName FROM SYS_User a WHERE a.[Status]='1'";
                    return broker.FillSQLDataSet(strcmd).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable Select(string strUserID, string strUserName,
                                                      int pageIndex,
                                                      int pageSize,
                                                      out int count)
        {
            string strcmd = @"SELECT a.UserID, a.UserName FROM SYS_User a WHERE a.[Status]='1'";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();

            if (!string.IsNullOrEmpty(strUserID))
            {
                strcmd += " and A.UserID like @UserID";
                dpc.AddWithValue("@UserID", "%" + strUserID + "%");
            }
            if (!string.IsNullOrEmpty(strUserName))
            {
                strcmd += " and A.UserName like @UserName";
                dpc.AddWithValue("@UserName", "%" + strUserName + "%");
            }
            strcmd += " ORDER BY A.UserID";
            try
            {
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
            }
            catch
            {
                throw;
            }
        }

        public static bool IsUser(string strCode)
        {
            string strcmd = @"SELECT COUNT(*) FROM SYS_UserRole WHERE UserID=@UserID";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@UserID", strCode);

            bool b = false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null)
                    {
                        if (Convert.ToInt32(obj.ToString().Trim()) > 0)
                        {
                            b = true;
                        }
                    }
                }
                catch
                {
                    b = false;
                    throw;
                }
            }
            return b;
        }

        public static void Del(string strCode, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_User WHERE UserID=@UserID";
            dpc.AddWithValue("@UserID", strCode);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static DataTable SelectUserProcess(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    string strcmd = @"
  SELECT a.*,b.ProcessLineName FROM [SYS_UserProcess] a 
  LEFT JOIN MD_ProcessLine b ON a.ProcessLineID=b.ProcessLineID";
                    strcmd +=" where 1=1 and a.UserID=@sUserID ";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@sUserID", strCode);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
        public static void DeleteDetail(string strCode, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_UserProcess WHERE UserID=@UserID";
            dpc.AddWithValue("@UserID", strCode);
            broker.ExecuteSQL(strcmd, dpc);
        }
    }
}
